package com.easyidea.its.util.excel;

import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.StringWriter;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.ResourceBundle;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.apache.commons.beanutils.ConstructorUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.easyidea.its.util.PropertiesUtil;
import com.easyidea.its.util.PropertyResourceBundle;
import com.easyidea.its.util.StringUtil;
import com.opensymphony.util.BeanUtils;
import com.opensymphony.util.FileManager;
import com.opensymphony.webwork.views.freemarker.FreemarkerManager;
import com.opensymphony.xwork.util.InstantiatingNullHandler;
import com.opensymphony.xwork.util.OgnlValueStack;

import freemarker.cache.StringTemplateLoader;
import freemarker.cache.TemplateLoader;
import freemarker.ext.beans.BeansWrapper;
import freemarker.ext.beans.ResourceBundleModel;
import freemarker.template.Configuration;
import freemarker.template.DefaultObjectWrapper;
import freemarker.template.SimpleHash;
import freemarker.template.Template;
import freemarker.template.TemplateException;

public class ExcelUtil {
	protected static Log log = LogFactory.getLog(ExcelUtil.class);

	private static Configuration conf = null;

	private static Configuration getConfiguration() {
		if (conf == null) {
			conf = new Configuration();
			conf.setObjectWrapper(new DefaultObjectWrapper());
			conf.setTemplateLoader(new StringTemplateLoader());
			try {
				InputStream in = FileManager.loadFile("freemarker.properties", FreemarkerManager.class);

				if (in != null) {
					Properties p = new Properties();
					p.load(in);
					p.remove("auto_import");
					conf.setSettings(p);
				}
			} catch (IOException e) {
				log.error("Error while loading freemarker settings from /freemarker.properties", e);
			} catch (TemplateException e) {
				log.error("Error while loading freemarker settings from /freemarker.properties", e);
			}
		}

		return conf;
	}

	/**
	 * Parsing the excel {@link InputStream} with the {@link WorkbookDefinition}
	 * which is defined the excel's columns content as the map key and value
	 * property , and the return map object will ignore the duplicated key that
	 * in excel input stream
	 * 
	 * @author Harrison
	 * @param is
	 * @param workbookDefinition
	 * @return
	 */
	public static Map parseWorkbookAsMap(InputStream is, MapWorkbookDefinition mapWorkbookDefinition) {
		Map map = new HashMap<Long, Double>();
		if (mapWorkbookDefinition == null || mapWorkbookDefinition.getMapSheetDefinitions() == null
				|| mapWorkbookDefinition.getMapSheetDefinitions().size() <= 0) {
			log.info("[INFO]no sheet definition with the workbook , system will return the map with no entries! ");
			return map;
		}
		Workbook rwb = null;
		try {
			rwb = Workbook.getWorkbook(is);
			for (int i = 0; i < mapWorkbookDefinition.getMapSheetDefinitions().size(); i++) {
				map = parseSheetAsMap(mapWorkbookDefinition.getMapSheetDefinitions().get(i), rwb.getSheet(i), map);
			}
		} catch (BiffException e) {
			log.error("[ERROR] parsing exception : " + e.getMessage() + " , and the stack is : " + e);
		} catch (IOException e) {
			log.error("[ERROR] parsing exception : " + e.getMessage() + " , and the stack is : " + e);
		}

		return map;
	}

	public static List parseWorkbook(InputStream is, WorkbookDefinition workbookDefinition) throws IOException {

		if (workbookDefinition == null || workbookDefinition.getSheetDefinitions() == null
				|| workbookDefinition.getSheetDefinitions().size() <= 0) {
			log.info("no sheet definition !");
			return null;
		}

		Workbook rwb = null;
		try {
			rwb = Workbook.getWorkbook(is);
			List<Object> entityList = new ArrayList<Object>();
			for (int i = 0; i < workbookDefinition.getSheetDefinitions().size(); i++) {
				List objs = parseSheet(workbookDefinition.getSheetDefinitions().get(i), rwb.getSheet(i));
				entityList.add(objs);
			}
			return entityList;
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * @author Harrison
	 * @param definition
	 * @param sheet
	 * @param map
	 * @return
	 */
	private static Map parseSheetAsMap(MapSheetDefinition definition, Sheet sheet, Map map) {
		int keyColIndex = -1;
		int valueColIndex = -1;
		int startLineIndex = -1;
		// definition the parsing start location in excel
		if (definition != null && sheet != null) {
			if (definition.isHasTitle()) {
				startLineIndex = 1;
				if (definition.getExcelTitleKeyColumn() != null && definition.getExcelTitleValueColumn() != null) {
					// use the title as the keyIndex and valueIndex , so parsing
					// with it and get the correct colIndex;
					int cols = sheet.getColumns();
					for (int i = 0; i < cols; i++) {
						Cell cell = sheet.getCell(i, 1); // reading the title
															// line
						String cellContent = cell.getContents();
						if (cellContent != null && cellContent.trim().equals(definition.getExcelTitleKeyColumn())) {
							keyColIndex = i;
						}
						if (cellContent != null && cellContent.trim().equals(definition.getExcelTitleValueColumn())) {
							valueColIndex = i;
						}
					}
					if (keyColIndex == -1 || valueColIndex == -1) {
						log.info("[INFO]no title named as the parameter assigned with "
								+ "excel on keyColumn and valueColumn");
						return map;
					}
					log.info("[INFO] the keyColumn Index is : " + keyColIndex + " and valueColumn Index is : "
							+ valueColIndex);
				} else if (definition.getExcelIndexKeyColumn() != -1 && definition.getExcelIndexValueColumn() != -1) {
					keyColIndex = definition.getExcelIndexKeyColumn();
					valueColIndex = definition.getExcelIndexValueColumn();
				}
			} else {
				startLineIndex = 0;
			}
			// put the excel data with assigned column index to map
			transportData2Map(sheet, map, keyColIndex, valueColIndex, startLineIndex);
		}
		return map;
	}

	/**
	 * @author Harrison
	 * @param sheet
	 * @param map
	 * @param keyColIndex
	 * @param valueColIndex
	 */
	private static void transportData2Map(Sheet sheet, Map map, int keyColIndex, int valueColIndex, int startLineIndex) {
		if (keyColIndex != -1 && valueColIndex != -1 && startLineIndex != -1) {
			for (int i = startLineIndex; i < sheet.getRows(); i++) {
				String keyContent = sheet.getCell(keyColIndex, i).getContents();
				String valueContent = sheet.getCell(valueColIndex, i).getContents();
				Long key = Long.valueOf(keyContent);
				if (keyContent != null && valueContent != null && !map.containsKey(key)) {
					map.put(key, Double.valueOf(valueContent));
				}
			}
		} else {
			log
					.info("[INFO] parameters not fit for the method transportData2Map, can not obtain the excel data correctly !");
		}
	}

	private static List parseSheet(SheetDefinition definition, Sheet sheet) {
		List objs = new ArrayList();
		boolean isHeader = false;
		try {
			for (int i = 0; i < sheet.getRows(); i++) {
				Cell[] cells = sheet.getRow(i);
				if (cells.length > definition.getColSize())
					continue;
				if (definition.isHasTitle() && !isHeader) {
					isHeader = true;
					continue;
				}

				Object obj = ConstructorUtils.invokeConstructor(definition.getEntityClass(), null);
				// DefaultBeanProvider dbp = new DefaultBeanProvider();
				OgnlValueStack stack = new OgnlValueStack();
				stack.getContext().put(InstantiatingNullHandler.CREATE_NULL_OBJECTS, Boolean.TRUE);
				stack.push(obj);

				for (int j = 0; j < cells.length; j++) {
					Cell cell = cells[j];
					if (definition.getColArray()[j] == null || definition.getColArray()[j].equals(""))
						continue;
					try {
						// BeanUtils.setValue(obj, definition.getColArray()[j],
						// cell.getContents());
						// dbp.setProperty(obj, definition.getColArray()[j],
						// cell.getContents());
						stack.setValue(definition.getColArray()[j], cell.getContents());
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				objs.add(obj);
			}
			return objs;

		} catch (NoSuchMethodException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return objs;
	}

	public static boolean writeWorkbook(OutputStream os, WorkbookDefinition workbookDefinition, Workbook workBook,
			String rowNum) throws IOException, BiffException {
		if (workbookDefinition == null || workbookDefinition.getSheetDefinitions() == null
				|| workbookDefinition.getSheetDefinitions().size() <= 0) {
			log.info("no sheet definition !");
			return false;
		}

		try {
			WritableWorkbook wwb = Workbook.createWorkbook(os, workBook);

			for (int i = 0; i < workbookDefinition.getSheetDefinitions().size(); i++) {
				createSheet(wwb.getSheet(0), workbookDefinition.getSheetDefinitions().get(i),rowNum);
			}
			wwb.write();
			wwb.close();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
			return false;
		}
		return true;
	}

	private static void createSheet(WritableSheet ws, SheetDefinition definition, String rowNum)
			throws RowsExceededException, WriteException, IOException, TemplateException {
		int rowNumber = 1;
		if(StringUtil.isNumeric(rowNum)){
			rowNumber = Integer.parseInt(rowNum);
		}
		Collection objs = definition.getRowEntitys();
		for (Iterator iter = objs.iterator(); iter.hasNext();) {
			Object obj = (Object) iter.next();
			String[] cols = definition.getColArray();
			for (int i = 0; i < cols.length; i++) {
				Label cell = null;
				if ("".equals(cols[i])) {
					cell = new Label(i, rowNumber, "");
				} else {
					Object value = null;
					if (isFreemarkerExpress(cols[i])) {
						try {
							value = getValueFromFreemarkerExpress(obj, cols[i]);
							if(log.isDebugEnabled()){
								log.debug("**************" + cols[i]);
							}
						} catch (TemplateException e) {
							continue;
						}

					} else {
						value = BeanUtils.getValue(obj, cols[i]);
					}

					cell = new Label(i, rowNumber, String.valueOf(value));
				}
				ws.addCell(cell);
			}
			rowNumber++;
		}
	}

	private static boolean isFreemarkerExpress(String propertyName) {
		if (propertyName.startsWith("${") || propertyName.indexOf("?") > 0)
			return true;
		else {
			return false;
		}
	}

	private static Object getValueFromFreemarkerExpress(Object obj, String col) throws IOException, TemplateException {
		Configuration cfg = getConfiguration();
		ResourceBundle RESOURCE_BUNDLE = new PropertyResourceBundle(PropertiesUtil.createI18nMap());
		ResourceBundleModel rsbm = new ResourceBundleModel(RESOURCE_BUNDLE, new BeansWrapper());

		SimpleHash model = new SimpleHash(cfg.getObjectWrapper());
		model.put("i18n", rsbm);
		model.put("obj", obj);

		// 国际化转换
		String templateSource = col;
		TemplateLoader templateLoader = cfg.getTemplateLoader();
		((StringTemplateLoader) templateLoader).putTemplate(templateSource, templateSource);
		Template template = conf.getTemplate(templateSource);
		StringWriter sw = new StringWriter();
		template.process(model, sw);
		return sw.toString();
	}
}
